package orest.back.inforecommend;

import java.sql.Connection;

import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class InfoRecommendSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static InfoRecommendSql m_builder = new InfoRecommendSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static InfoRecommendSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    /**
     * 获得科技成果列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getKjcgInfoList(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.*,S.ITEM_9 UNIT_NAME FROM T_Kjcg T LEFT JOIN T_UNIT S ON T.UNITID = S.UNITID WHERE 1=1 ");
        String keyword = kit.getParameter("keyword","");
        //成果名称
        if(!"".equals(keyword)){
        	sb.append(" AND T.KJCG_ITEM_3 LIKE '%").append(keyword).append("%' ");
        }
        /*
        if(!keyword.equals("")){
        	sb.append(" AND (T.TOPICWORDS LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.KJCG_ITEM_3 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR S.ITEM_9 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.KJCG_ITEM_13 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.KJCG_ITEM_13_1 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.KJCG_ITEM_13_2 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.KJCG_ITEM_13_3 LIKE '%").append(keyword).append("%'");
        	
        	sb.append(" OR T.KJCG_ITEM_127_1 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.COOPERUNIT LIKE '%").append(keyword).append("%'");
        	
        	sb.append(" OR T.COCREATOR LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.RECOMMDEPT LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.KJCG_ITEM_9 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.KJCG_ITEM_13_1S LIKE '%").append(keyword).append("%') ");
        }
        */
        
        //类别
        String newenergy = kit.getParameter("KJCG_ITEM_CGLB","");
        if(newenergy!=null&&!newenergy.equals("")){
        	sb.append(" AND T.KJCG_ITEM_CGLB = '").append(newenergy).append("' ");
        }
        
        //完成单位
        String finishunit = kit.getParameter("FINISHUNIT", "");
        if(finishunit!=null&&!"".equals(finishunit)){
        	sb.append(" AND S.ITEM_9 like '%").append(finishunit).append("%' ");
        }
        
        //年份
        String searchyear = kit.getParameter("SEARCHYEAR", "");
        if(searchyear!=null && !"".equals(searchyear)){
        	sb.append(" AND T.KJCG_ITEM_YEAR = '").append(searchyear).append("' ");
        }
        
        //推荐到频道
        String channelshow = kit.getParameter("CHANNELSHOW", "");
        if(channelshow!=null && !"".equals(channelshow)){
        	sb.append(" AND T.CHANNELSHOW='").append(channelshow).append("' ");
        }
        
        //推荐到首页
        String homepageshow = kit.getParameter("HOMEPAGESHOW", "");
        if(homepageshow!=null && !"".equals(homepageshow)){
        	sb.append(" AND T.HOMEPAGESHOW='").append(homepageshow).append("' ");
        }
        
        
        if(channelshow.equals("1")&&homepageshow.equals("1")){
        	sb.append(" ORDER BY HOMEPAGESORTNUM");
        }else if(channelshow.equals("1")){
        	sb.append(" ORDER BY CHANNELSORTNUM");
        }else if(homepageshow.equals("1")){
        	sb.append(" ORDER BY HOMEPAGESORTNUM");
        }
        
        
        //sb.append(" ORDER BY SUBMITDATE DESC");
        return sb.toString();
    }
    
    
    
    
    /**
     * 科技成果推荐状态修改
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String changeKjcgStatus(String expertUuids,String fieldname,String status)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE T_KJCG SET ").append(fieldname);
        sb.append(" = '").append(status).append("'");
        String expertUuidArr[] = expertUuids.split(",");
    	sb.append(" WHERE KJCGID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    
    
    /**
     * 获得专家列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getExpertPhotoMaintainList(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.*,S.ITEM_9 UNIT_NAME FROM T_EXPERT T LEFT JOIN T_UNIT S ON T.UNITID = S.UNITID WHERE 1=1 ");
        
        //专家姓名
        String keyword = kit.getParameter("keyword","");
        if(keyword!=null && !"".equals(keyword)){
        	sb.append(" AND T.ITEM_180 LIKE '%").append(keyword).append("%' ");
        }
        /*
        if(!keyword.equals("")){
        	sb.append(" AND (T.ITEM_180 LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.WORKSPECIALITY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR S.ITEM_9 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.GRADUATESCHOOL LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.SPECIALITY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.DEGREE LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.CLAN LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.NATIONALITY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.DUTY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.ITEM_185 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.ITEM_37 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.EXPINTRO LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.ACHIEVEMENT LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.STRONGTRAIT LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        }
        */
        
        //专业
        String WORKSPECIALITY = kit.getParameter("WORKSPECIALITY","");
        if(WORKSPECIALITY!=null&&!WORKSPECIALITY.equals("")){
        	sb.append(" AND T.WORKSPECIALITY LIKE '%").append(WORKSPECIALITY).append("%' ");
        }
        
        //所在单位
        String UNIT_NAME = kit.getParameter("UNIT_NAME","");
        if(WORKSPECIALITY!=null&&!WORKSPECIALITY.equals("")){
        	sb.append(" AND S.ITEM_9 LIKE '%").append(UNIT_NAME).append("%' ");
        }
        
       
        //sb.append(" ORDER BY SUBMITDATE DESC");
        return sb.toString();
    }
    
    
    
    
    
    
    
    /**
     * 获得专家列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getExpertInfoList(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.*,S.ITEM_9 UNIT_NAME FROM T_EXPERT T LEFT JOIN T_UNIT S ON T.UNITID = S.UNITID WHERE 1=1 ");
        
        //专家姓名
        String keyword = kit.getParameter("keyword","");
        if(keyword!=null && !"".equals(keyword)){
        	sb.append(" AND T.ITEM_180 LIKE '%").append(keyword).append("%' ");
        }
        /*
        if(!keyword.equals("")){
        	sb.append(" AND (T.ITEM_180 LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.WORKSPECIALITY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR S.ITEM_9 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.GRADUATESCHOOL LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.SPECIALITY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.DEGREE LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.CLAN LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.NATIONALITY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.DUTY LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.ITEM_185 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.ITEM_37 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.EXPINTRO LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.ACHIEVEMENT LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.STRONGTRAIT LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        }
        */
        
        //专业
        String WORKSPECIALITY = kit.getParameter("WORKSPECIALITY","");
        if(WORKSPECIALITY!=null&&!WORKSPECIALITY.equals("")){
        	sb.append(" AND T.WORKSPECIALITY LIKE '%").append(WORKSPECIALITY).append("%' ");
        }
        
        //所在单位
        String UNIT_NAME = kit.getParameter("UNIT_NAME","");
        if(WORKSPECIALITY!=null&&!WORKSPECIALITY.equals("")){
        	sb.append(" AND S.ITEM_9 LIKE '%").append(UNIT_NAME).append("%' ");
        }
        
        //推荐到频道
        String channelshow = kit.getParameter("CHANNELSHOW", "");
        if(channelshow!=null && !"".equals(channelshow)){
        	sb.append(" AND T.CHANNELSHOW='").append(channelshow).append("' ");
        }
        
        //推荐到首页
        String homepageshow = kit.getParameter("HOMEPAGESHOW", "");
        if(homepageshow!=null && !"".equals(homepageshow)){
        	sb.append(" AND T.HOMEPAGESHOW='").append(homepageshow).append("' ");
        }
        
        if(channelshow.equals("1")&&homepageshow.equals("1")){
        	sb.append(" ORDER BY HOMEPAGESORTNUM");
        }else if(channelshow.equals("1")){
        	sb.append(" ORDER BY CHANNELSORTNUM");
        }else if(homepageshow.equals("1")){
        	sb.append(" ORDER BY HOMEPAGESORTNUM");
        }
        
        //sb.append(" ORDER BY SUBMITDATE DESC");
        return sb.toString();
    }
    
    
    
    /**
     * 科技成果推荐状态修改
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String changeExpertStatus(String expertUuids,String fieldname,String status)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE T_EXPERT SET ").append(fieldname);
        sb.append(" = '").append(status).append("'");
        String expertUuidArr[] = expertUuids.split(",");
    	sb.append(" WHERE EXPERTID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    
    
    
    /**
     * 获得单位信息列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getUnitInfoList(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.* FROM T_UNIT T WHERE 1=1 ");
        
        //单位名称
        String keyword = kit.getParameter("keyword","");
        if(!keyword.equals("")){
        	sb.append(" AND T.ITEM_9 LIKE '%").append(keyword).append("%' ");
        	/*
        	sb.append(" AND (T.ITEM_9 LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.ITEM_37 LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.UNITINTRO LIKE '%").append(keyword).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        	*/
        }
        
        //所在省市
        String WORKSPECIALITY = kit.getParameter("ITEM_64","");
        if(WORKSPECIALITY!=null&&!WORKSPECIALITY.equals("")){
        	sb.append(" AND T.ITEM_64 ='").append(WORKSPECIALITY).append("' ");
        }
        
        //推荐到频道
        String channelshow = kit.getParameter("CHANNELSHOW", "");
        if(channelshow!=null && !"".equals(channelshow)){
        	sb.append(" AND T.CHANNELSHOW='").append(channelshow).append("' ");
        }
        
        //推荐到首页
        String homepageshow = kit.getParameter("HOMEPAGESHOW", "");
        if(homepageshow!=null && !"".equals(homepageshow)){
        	sb.append(" AND T.HOMEPAGESHOW='").append(homepageshow).append("' ");
        }
        
        
        if(channelshow.equals("1")&&homepageshow.equals("1")){
        	sb.append(" ORDER BY HOMEPAGESORTNUM");
        }else if(channelshow.equals("1")){
        	sb.append(" ORDER BY CHANNELSORTNUM");
        }else if(homepageshow.equals("1")){
        	sb.append(" ORDER BY HOMEPAGESORTNUM");
        }
        
        //sb.append(" ORDER BY SUBMITDATE DESC");
        return sb.toString();
    }
    
    
    
    /**
     * 科研单位推荐状态修改
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String changeUnitStatus(String unitUuids,String fieldname,String status)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append("UPDATE T_UNIT SET ").append(fieldname);
        sb.append(" = '").append(status).append("'");
        String expertUuidArr[] = unitUuids.split(",");
    	sb.append(" WHERE UNITID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    
}
